package com.max.spark.mysql

import scalikejdbc.{DB, SQL}

import java.sql.Connection


object ScalikeJdbcApp {
  case class Student(age:Int,name:String,sex:Int)
  //查询
  def query()={
    import scalikejdbc._
    val students = DB readOnly(implicit session => {
      sql"select * from accounter ".map(rs => Student(rs.int(1), rs.string("aname"), rs.int(3))).list().apply()
    })
    students.foreach(stu=>println(s"id:${stu.age},name:${stu.name},count:${stu.sex}"))
    //查询数据并返回单个列 并将列数据封装到集合中
  }

  //测试库查询
  def test()={
    import scalikejdbc._
    val students = NamedDB('test) readOnly(implicit session => {
      sql"select * from accounter ".map(rs => Student(rs.int(1), rs.string(2), rs.int(3))).list().apply()
    })
    students.foreach(println)
  }

  //插入
  def insert()={
    import scalikejdbc._
    val i = DB.autoCommit { implicit session =>
      SQL("insert into accounter(aname,balance) value(?,?)").bind("te", "1").update().apply()
    }
    println(i)
  }

  //更新
  def update()={
    import scalikejdbc._
    DB.localTx{implicit session=>
      sql"update accounter set aname=? where aid=2".bind("123").update().apply()
    }
  }
  //更新修改
  def update1()={
    val s = DB autoCommit (implicit session => {
      import scalikejdbc._
      sql"update accounter set aname='ls' where aid=?".bind(2).update().apply()
    })
  }

  //删除数据
  def del()={
    import scalikejdbc._
    val row=DB autoCommit(implicit session=>{
      sql"delete from accounter where aid=?".bind(1).update().apply()
    })
    println(s"受影响的行数+$row")
  }

  def data(): Unit ={
    import scalikejdbc._
    val result: List[String] = NamedDB('zcw) readOnly(implicit session => {
      sql"select username from tb_user".map(rs =>{
        rs.string("username") }).list().apply()
    })
    result.foreach(println(_))
  }


  def main(args: Array[String]): Unit = {
    //解析配置文件
//    DBs.setupAll()
    //加载指定数据库的配置文件
//    DBs.setup('test)
    //query()
    //test()
    //insert()
    //update()
    // update1()
    //del()

    //连接池
    import scalikejdbc._
    val conn: Connection = ConnectionPool.borrow()
    val tuples = using(DB(ConnectionPool.borrow())) {
      db =>
        db.readOnly { implicit session =>
          sql"select * from accounter".map(rs => (rs.int(1), rs.string(2), rs.int(3))).list().apply()
        }
    }
    tuples.foreach(println(_))
  }
}
